package com.hnust.dao;

import com.hnust.domain.Course;
import com.hnust.domain.CourseGroup;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.ResultType;
import org.apache.ibatis.annotations.Select;
import com.hnust.domain.Class;

import java.util.List;

import com.hnust.domain.Teacher;
import com.hnust.domain.record.CourseGroupInfo;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

/**
 *创建人：张汝往,游磊
 *创建时间：2020/10/8
 *描述：
 */
@Mapper
@Repository
public interface CourseGroupDao {

    //根据学院ID获取课程组总数
    @Select("select count(*) from course_group where col_id=#{collegeId}")
    @ResultType(Integer.class)
    public Integer findCourseGroupCountByCollegeId(String collegeId);

    // 查询某个学院下所有的课程组名字
    @Select("select * from course_group where col_id =#{colId}")
    @ResultType(Course.class)
    List<CourseGroup> getCourseGroupListByColId(String colId);

    // 查询所有的开课时间
    @Select("select open_time from course_group group by open_time;")
    @ResultType(String.class)
    List<String> getCourseGroupOpenTime();

    //根据搜索条件获取课程组信息
    @SelectProvider(type =searchCourseGroupProvider.class,method = "searchCourseGroupSql")
    @ResultType(CourseGroup.class)
    public List<CourseGroup> searchCourseGroup(String collegeId,String courseName,
                                               String time,String teacherName,
                                               String grade,String majorName,String claNumber);

    class searchCourseGroupProvider{
        public String searchCourseGroupSql(
                String collegeId,String courseName,
                String time,String teacherName,
                String grade,String majorName,String claNumber
        ){
            String sql="select course_group.* from course_group,course,teacher,class,major,tea_course_group,class_course_group " +
                    " where course_group.col_id=#{collegeId}";
            if(courseName!=null){
                sql=sql+" and course.name like concat('%',#{courseName},'%') ";
            }
            if(time!=null){
                sql=sql+" and course_group.open_time=#{time}";
            }
            if(teacherName!=null){
                sql=sql+" and teacher.name like concat('%',#{teacherName},'%')";
            }
            if(grade!=null){
                sql=sql+" and class.grade=#{grade}";
            }
            if(majorName!=null){
                sql=sql+" and major.name like concat('%',#{majorName},'%')";
            }
            if(claNumber!=null){
                sql=sql+" and class.cla_number=#{claNumber}";
            }
            sql=sql+" and course_group.cou_gro_id=tea_course_group.cou_gro_id and tea_course_group.tea_id=teacher.tea_id";
            sql=sql+" and class_course_group.cla_id=class.cla_id and class_course_group.cou_gro_id=course_group.cou_gro_id";
            sql=sql+" and class.maj_id=major.maj_id";
            sql=sql+" and course.cou_id=course_group.cou_id";
            return sql;
        }
    }

    //查询课程组的所有老师
    @Select("select teacher.* from teacher,course_group,tea_course_group where " +
            "teacher.tea_id=tea_course_group.tea_id and " +
            "course_group.cou_gro_id=tea_course_group.cou_gro_id" +
            " and course_group.cou_gro_id=#{id}")
    @ResultType(Teacher.class)
    public List<Teacher> findAllTeacherByCourseGroupId(String id);

    //查询课程组的所有班级
    @Select("select class.* from class,course_group,class_course_group where " +
            "class.cla_id=class_course_group.cla_id and " +
            "course_group.cou_gro_id=class_course_group.cou_gro_id" +
            " and course_group.cou_gro_id=#{id}")
    @ResultType(Class.class)
    public List<Class> findAllClassByCourseGroupId(String id);


    //删除课程组
    @Delete("delete from course_group where cou_gro_id=#{id}")
    @ResultType(Boolean.class)
    public Boolean deleteCourseGroupById(String id);

    //根据ID查询课程组
    @Select("select * from course_group where cou_gro_id=#{id}")
    @ResultType(CourseGroup.class)
    public CourseGroup findCourseGroupById(String id);

    //更新课程组信息
    @Update("update course_group set name=#{name},open_time=#{openTime},cou_id=#{courseId} where cou_gro_id=#{" +
            "courseGroupId}")
    @ResultType(Boolean.class)
    public Boolean modifyCourseGroup(String courseGroupId,String openTime,String name,String courseId);

    //添加课程组信息
    @Insert("insert into course_group " +
            "values(#{uuid},#{name},#{time},#{describe},#{colId},#{couId})")
    @ResultType(Boolean.class)
    public Boolean addCourseGroup(String uuid,String name,String time,String describe,String colId,String couId);

    //查询学院所有课程组
    @Select("select * from course_group where col_id=#{collegeId}")
    @ResultType(CourseGroup.class)
    public List<CourseGroup> findAllCourseGroupByCollegeId(String collegeId);

    //根据老师ID和课程组ID
    @Select("select group_leader from tea_course_group where tea_id=#{teacherId} and cou_gro_id=#{courseGroupId}")
    @ResultType(Integer.class)
    public Integer findCourseGroupLeaderByCourseGroupIdAndTeacherId(String courseGroupId,String teacherId);

    //插入老师课程组记录
    @Insert("insert into tea_course_group values(#{uuid},#{isLeader},#{teacherId},#{courseGroupId})")
    public Boolean insertTeacherInCourseGroup(String uuid,Integer isLeader,String courseGroupId,String teacherId);

    //删除老师课程组记录
    @Delete("delete from tea_course_group where tea_id=#{teacherId} and cou_gro_id=#{courseGroupId}")
    public Boolean deleteTeacherInCourseGroup(String courseGroupId,String teacherId);

    //插入班级课程组记录
    @Insert("insert into class_course_group values(#{uuid},#{classId},#{courseGroupId})")
    public Boolean insertClassInCourseGroup(String uuid,String classId,String courseGroupId);

    //删除班级课程组记录
    @Delete("delete from class_course_group where cla_id=#{classId} and cou_gro_id=#{courseGroupId}")
    public Boolean deleteClassInCourseGroup(String classId,String courseGroupId);

    //更新课程组组长
    @Update("update tea_course_group set group_leader=#{leader} where tea_id=#{teacherId} and cou_gro_id=#{courseGroupId}")
    public void updateLeaderInTeacherCourseGroup(String teacherId, String courseGroupId, Integer leader);
}
